-- For a given order or event, returns whether VAT should be charged
-- PARAMETERS
-- @orgCode             The financial entity (org) for the order.
--                      If passed empty the default org is assumed.
-- @billToId            Bill to contact Id.
-- @shipToId            Ship to contact Id.
-- @shipToCountry       The currently selected ship to country code. Accepts either a country name or country code.
--                      This may not necessarily be the ship to contacts ship to country. It may have been manually changed on the order.
-- @applyVat OUT        Returns a bit to indicate whether VAT should be charged for this order.
-- @vatCountryCode OUT  Returns the country code that VAT is being charged based on.
--                      Will always be empty unless @applyVat == 1

-- The procedure also returns single row resultset containing the value returned by @applyVat OUT and @vatCountryCode OUT

CREATE PROCEDURE [dbo].[asi_IsVatTaxableOrder]
    @orgCode varchar(5),
    @billToID varchar(10),
    @shipToID varchar(10),
    @shipToCountry varchar(25),
    @applyVat bit = NULL OUT,
    @vatCountryCode varchar(2) = NULL OUT
    
AS
BEGIN

    -- declare variables    
    DECLARE @taxOnShipTo bit
    DECLARE @orgVatCountryCode varchar(2)
    DECLARE @contactIsVatExempt bit
    DECLARE @contactVatRegNumber varchar(25)
    DECLARE @contactVatCountryCode varchar(2)
    DECLARE @billToVatCountryCode varchar(2)
    DECLARE @shipToVatCountryCode varchar(2)
    
    -- default the return value to no vat
    SET @applyVat = 0
    SET @vatCountryCode = ''

    -- check we are licensed for VAT
    IF NOT EXISTS (SELECT 1
                     FROM [dbo].[LicenseLegacyList]
                    WHERE [LegacyLicenseCode]='VAT')
    BEGIN
        GOTO ReturnValue
    END

    -- get the org's VAT country code
    -- as part of this we check that it is a vat org and it has a vat country defined
    -- if no org was specified, then assume the default org
    IF LEN(ISNULL(@orgCode, ''))=0
    BEGIN
        SELECT @orgCode = o.[OrgCode], @orgVatCountryCode = o.[VATCountry]
          FROM [dbo].[Org_Control] o
         WHERE o.[DefaultFlag]=1 AND o.[UseVATTaxation]=1 AND LEN(o.[VATCountry])>0
    END
    ELSE
    BEGIN
        SELECT @orgVatCountryCode = o.[VATCountry]
          FROM [dbo].[Org_Control] o
         WHERE o.[OrgCode]=@orgCode AND o.[UseVATTaxation]=1 AND LEN(o.[VATCountry])>0
    END
    IF @@ROWCOUNT=0
    BEGIN
        GOTO ReturnValue
    END
    
    -- now we have established the org is correctly configured for vat,
    -- assume we are going to charge vat
    SET @applyVat = 1
    SET @vatCountryCode = @orgVatCountryCode

    -- determine whether to use TaxOnShipTo option
    -- if the setting cannot be found, default to false
    SELECT @taxOnShipTo = CASE WHEN [ShortValue]='YES' THEN 1 ELSE 0 END
      FROM [dbo].[System_Params]
     WHERE [ParameterName] = 'AR_Control.TaxOnShipTo'  
    IF @@ROWCOUNT=0
    BEGIN
        SET @taxOnShipTo = 0
    END     

    -- process based on the taxation method
    IF @taxOnShipTo = 1
    BEGIN
        
        -- do we have a ship to id
        IF LEN(ISNULL(@shipToID, ''))=0
        BEGIN
            GOTO ReturnValue
        END
        
        -- load vat details from the ship to contact
        SELECT @contactIsVatExempt = ISNULL(nf.[USE_VAT_TAXATION],0),
               @contactVatRegNumber = ISNULL(nf.[VAT_REG_NUMBER],''),
               @contactVatCountryCode = ISNULL(nf.[VAT_COUNTRY],'')
          FROM [dbo].[Name] n
               LEFT OUTER JOIN [dbo].[Name_Fin] nf ON n.[ID] = nf.[ID]
         WHERE n.[ID]=@shipToID
        IF @@ROWCOUNT=0
        BEGIN
            GOTO ReturnValue
        END
         
        -- if the contact is vat exempt, go no further
        IF @contactIsVatExempt=1
        BEGIN
            SET @applyVat = 0
            GOTO ReturnValue
        END
        
        -- establish the vat country code of the ship to country
        -- if no country was specified, or we can't find a match, assume its the org vat country code
        SET @shipToVatCountryCode = @orgVatCountryCode
        IF LEN(ISNULL(@shipToCountry,''))>0
        BEGIN
            -- check the ship to country exists
            -- we could have been supplied either a country code or a country name    
            IF LEN(@shipToCountry)<=2
            BEGIN
                SELECT @shipToVatCountryCode = c.[VAT_COUNTRY_CODE]
                  FROM [dbo].[Country_Names] c
                 WHERE c.[COUNTRY_CODE] = @shipToCountry
            END
            ELSE
            BEGIN
                SELECT @shipToVatCountryCode = c.[VAT_COUNTRY_CODE]
                  FROM [dbo].[Country_Names] c
                 WHERE c.[COUNTRY] = @shipToCountry
            END
            -- if the above doesn't find anything, @shipToVatCountryCode remains unchanged
        END
        
        -- is the contact considered vat registered
        IF LEN(@contactVatRegNumber)>0 AND LEN(@contactVatCountryCode)>0 AND @contactVatCountryCode=@shipToVatCountryCode
        BEGIN
            -- not taxable if different EU country from org
            IF (@contactVatCountryCode != @orgVatCountryCode)
            BEGIN
                SET @applyVat = 0
                GOTO ReturnValue
            END
        END
    
        -- if we have a ship to vat country code, then charge vat
        IF LEN(@shipToVatCountryCode)>0
        BEGIN
            SET @vatCountryCode = @shipToVatCountryCode
        END
        ELSE
        BEGIN
            SET @applyVat = 0
        END
         
    END
    ELSE
    BEGIN
        
        -- do we have a bill to id
        IF LEN(ISNULL(@billToID, ''))=0
        BEGIN
            GOTO ReturnValue
        END
        
        -- load vat details from the bill to contact
        SELECT @contactIsVatExempt = ISNULL(nf.[USE_VAT_TAXATION],0),
               @contactVatRegNumber = ISNULL(nf.[VAT_REG_NUMBER],''),
               @contactVatCountryCode = ISNULL(nf.[VAT_COUNTRY],'')
          FROM [dbo].[Name] n
               LEFT OUTER JOIN [dbo].[Name_Fin] nf ON n.[ID] = nf.[ID]
         WHERE n.[ID]=@billToID
        IF @@ROWCOUNT=0
        BEGIN
            GOTO ReturnValue
        END
         
        -- if the contact is vat exempt, go no further
        IF @contactIsVatExempt=1
        BEGIN
            SET @applyVat = 0
            GOTO ReturnValue
        END

        -- establish the vat country code for the bill to contact
        -- if the contact has a specific vat country code defined, use that
        -- otherwise establish it from their billing address
        IF LEN(@contactVatCountryCode)>0
        BEGIN
            SET @billToVatCountryCode = @contactVatCountryCode
        END
        ELSE
        BEGIN
            -- get the vat country code from the contacts billing country
            -- if the contact has no billing country, assume the org vat country
            SELECT @billToVatCountryCode = c.[VAT_COUNTRY_CODE]
              FROM [dbo].[Name_Address] n
                   INNER JOIN [dbo].[Country_Names] c ON n.[COUNTRY] = c.[COUNTRY]
             WHERE n.[ID] = @billToID AND n.[PREFERRED_BILL]=1 AND LEN(ISNULL(n.[COUNTRY],''))>0
            IF @@ROWCOUNT=0
            BEGIN
                SET @billToVatCountryCode = @orgVatCountryCode
            END
        END
        
        -- is the contact considered vat registered
        IF LEN(@contactVatRegNumber)>0
        BEGIN
            -- not taxable if different EU country from org
            IF (@billToVatCountryCode != @orgVatCountryCode)
            BEGIN
                SET @applyVat = 0
                GOTO ReturnValue
            END
        END

        -- if we have a bill to vat country code, then charge vat
        IF LEN(@billToVatCountryCode)>0
        BEGIN
            SET @vatCountryCode = @billToVatCountryCode
        END
        ELSE
        BEGIN
            SET @applyVat = 0
        END
        
    END

ReturnValue:    

    -- if we arent applying vat, then clear any country code
    IF @applyVat = 0
    BEGIN
        SET @vatCountryCode = ''
    END

    -- return the values as a resultset for desktop
    SELECT @applyVat AS [ApplyVat], @vatCountryCode AS [VatCountryCode]

END

GO